package com.huaxia.dao.performance;

import com.huaxia.pojo.performance.BranchReach;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/05/20 11:22
 */
public interface BranchReachMapper {

    @Select("select y.branchsname, round((y.ycb-qy.ycb)/10000,0) branchMonthIncrease,yr.num,yr.saleDeptNum,yr.branchReachYield,round(j.branchQuarterInSure/10000,0)branchQuarterInSure,round((j.branchQuarterInSure-qj.branchQuarterInSure)/10000,0) branchQuarterIncrease,round(n.branchYearInSure/10000,0)branchYearInSure,round((n.branchYearInSure-qn.branchYearInSure)/10000,0) branchYearIncrease, nr.yearReachYield from\n" +
            "(select bcb.BRANCHSNAME,bcb.zcb-byt.zyt ycb from \n" +
            "(select SD_BRANCH.BRANCHSNAME,NVL(cb.monthInSure,0) zcb from\n" +
            "(select BRANCHCODE, SUM(WRITTENSTADPREM)monthInSure from FACT_YX_PREPREM_LIST_day where TO_CHAR(ISSUE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') AND RNFLAG='N' GROUP BY BRANCHCODE)cb RIGHT JOIN\n" +
            "SD_BRANCH ON cb.BRANCHCODE=SD_BRANCH.BRANCHCODE)bcb left join\n" +
            "(select SD_BRANCH.BRANCHSNAME,NVL(yt.monthYt,0) zyt from\n" +
            "(select BRANCHCODE, SUM(WRITTENSTADPREM)monthYt from FACT_YX_PREPREM_LIST_day where TO_CHAR(YT_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM')  AND RNFLAG='N' GROUP BY BRANCHCODE)yt right join \n" +
            "SD_BRANCH ON yt.BRANCHCODE=SD_BRANCH.BRANCHCODE)byt\n" +
            "on bcb.BRANCHSNAME=byt.BRANCHSNAME)y,\n" +
            "(select bcb.BRANCHSNAME,bcb.zcb-byt.zyt ycb from \n" +
            "(select SD_BRANCH.BRANCHSNAME,NVL(cb.monthInSure,0) zcb from\n" +
            "(select BRANCHCODE, SUM(WRITTENSTADPREM)monthInSure from FACT_YX_PREPREM_LIST_day where TO_CHAR(ISSUE_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY')-1 AND TO_CHAR(ISSUE_DATE,'MM')=TO_CHAR(SYSDATE,'MM') AND RNFLAG='N' GROUP BY BRANCHCODE)cb RIGHT JOIN\n" +
            "SD_BRANCH ON cb.BRANCHCODE=SD_BRANCH.BRANCHCODE)bcb left join\n" +
            "(select SD_BRANCH.BRANCHSNAME,NVL(yt.monthYt,0) zyt from \n" +
            "(select BRANCHCODE, SUM(WRITTENSTADPREM)monthYt from FACT_YX_PREPREM_LIST_day where TO_CHAR(YT_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY')-1 AND TO_CHAR(YT_DATE,'MM')=TO_CHAR(SYSDATE,'MM')  AND RNFLAG='N' GROUP BY BRANCHCODE)yt right join \n" +
            "SD_BRANCH ON yt.BRANCHCODE=SD_BRANCH.BRANCHCODE)byt\n" +
            "on bcb.BRANCHSNAME=byt.BRANCHSNAME)qy,\n" +
            "(select bcb.BRANCHSNAME,bcb.zcb-byt.zyt branchQuarterInSure from \n" +
            "(select SD_BRANCH.BRANCHSNAME,NVL(cb.monthInSure,0) zcb from\n" +
            "(select BRANCHCODE, SUM(WRITTENSTADPREM)monthInSure from FACT_YX_PREPREM_LIST_day where TO_CHAR(ISSUE_DATE,'q')=TO_CHAR(SYSDATE,'q') and TO_CHAR(ISSUE_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY') AND RNFLAG='N' GROUP BY BRANCHCODE)cb RIGHT JOIN\n" +
            "SD_BRANCH ON cb.BRANCHCODE=SD_BRANCH.BRANCHCODE)bcb left join\n" +
            "(select SD_BRANCH.BRANCHSNAME,NVL(yt.monthYt,0) zyt from\n" +
            "(select BRANCHCODE, SUM(WRITTENSTADPREM)monthYt from FACT_YX_PREPREM_LIST_day where TO_CHAR(YT_DATE,'q')=TO_CHAR(SYSDATE,'q') and TO_CHAR(YT_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY')  AND RNFLAG='N' GROUP BY BRANCHCODE)yt right join \n" +
            "SD_BRANCH ON yt.BRANCHCODE=SD_BRANCH.BRANCHCODE)byt\n" +
            "on bcb.BRANCHSNAME=byt.BRANCHSNAME)j,\n" +
            " (select bcb.BRANCHSNAME,bcb.zcb-byt.zyt branchQuarterInSure from \n" +
            "(select SD_BRANCH.BRANCHSNAME,NVL(cb.monthInSure,0) zcb from\n" +
            "(select BRANCHCODE, SUM(WRITTENSTADPREM)monthInSure from FACT_YX_PREPREM_LIST_day where TO_CHAR(ISSUE_DATE,'q')=TO_CHAR(SYSDATE,'q') and TO_CHAR(ISSUE_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY')-1 AND RNFLAG='N' GROUP BY BRANCHCODE)cb RIGHT JOIN\n" +
            "SD_BRANCH ON cb.BRANCHCODE=SD_BRANCH.BRANCHCODE)bcb left join\n" +
            "(select SD_BRANCH.BRANCHSNAME,NVL(yt.monthYt,0) zyt from\n" +
            "(select BRANCHCODE, SUM(WRITTENSTADPREM)monthYt from FACT_YX_PREPREM_LIST_day where TO_CHAR(YT_DATE,'q')=TO_CHAR(SYSDATE,'q') and TO_CHAR(YT_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY')-1  AND RNFLAG='N' GROUP BY BRANCHCODE)yt right join \n" +
            "SD_BRANCH ON yt.BRANCHCODE=SD_BRANCH.BRANCHCODE)byt\n" +
            "on bcb.BRANCHSNAME=byt.BRANCHSNAME)qj,\n" +
            " (select bcb.BRANCHSNAME,bcb.zcb-byt.zyt branchYearInSure from \n" +
            "(select SD_BRANCH.BRANCHSNAME,NVL(cb.yearInSure,0) zcb from\n" +
            "(select BRANCHCODE, SUM(WRITTENSTADPREM)yearInSure from FACT_YX_PREPREM_LIST_day where  TO_CHAR(ISSUE_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY') AND RNFLAG='N' GROUP BY BRANCHCODE)cb RIGHT JOIN\n" +
            "SD_BRANCH ON cb.BRANCHCODE=SD_BRANCH.BRANCHCODE)bcb left join\n" +
            "(select SD_BRANCH.BRANCHSNAME,NVL(yt.yearYt,0) zyt from\n" +
            "(select BRANCHCODE, SUM(WRITTENSTADPREM)yearYt from FACT_YX_PREPREM_LIST_day where  TO_CHAR(YT_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY')  AND RNFLAG='N' GROUP BY BRANCHCODE)yt right join \n" +
            "SD_BRANCH ON yt.BRANCHCODE=SD_BRANCH.BRANCHCODE)byt\n" +
            "on bcb.BRANCHSNAME=byt.BRANCHSNAME)n,\n" +
            " (select bcb.BRANCHSNAME,bcb.zcb-byt.zyt branchYearInSure from \n" +
            "(select SD_BRANCH.BRANCHSNAME,NVL(cb.yearInSure,0) zcb from\n" +
            "(select BRANCHCODE, SUM(WRITTENSTADPREM)yearInSure from FACT_YX_PREPREM_LIST_day where  TO_CHAR(ISSUE_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY')-1 AND RNFLAG='N' GROUP BY BRANCHCODE)cb RIGHT JOIN\n" +
            "SD_BRANCH ON cb.BRANCHCODE=SD_BRANCH.BRANCHCODE)bcb left join\n" +
            "(select SD_BRANCH.BRANCHSNAME,NVL(yt.yearYt,0) zyt from\n" +
            "(select BRANCHCODE, SUM(WRITTENSTADPREM)yearYt from FACT_YX_PREPREM_LIST_day where  TO_CHAR(YT_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY')-1  AND RNFLAG='N' GROUP BY BRANCHCODE)yt right join \n" +
            "SD_BRANCH ON yt.BRANCHCODE=SD_BRANCH.BRANCHCODE)byt\n" +
            "on bcb.BRANCHSNAME=byt.BRANCHSNAME)qn,\n" +
            "(select sb.BRANCHSNAME,a.num,sb.saleDeptNum,round(NVL(a.num,0)/sb.saledeptnum,2)*100 branchReachYield from(\n" +
            "select a.branchcode,count(a.SALEDEPTCODE) NUM from (\n" +
            "select a.BRANCHCODE,a.SALEDEPTCODE, a.monthInSUre-b.monthYt zcb from\n" +
            "(select t.branchcode, t.SALEDEPTCODE, SUM(t.WRITTENSTADPREM)monthInSure from FACT_YX_PREPREM_LIST_day t where TO_CHAR(t.ISSUE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM')  \n" +
            "AND t.RNFLAG='N' AND t.DEPTCODE1 IS NOT NULL GROUP BY t.SALEDEPTCODE,t.branchcode)a left join \n" +
            "(select t.branchcode, t.SALEDEPTCODE, SUM(t.WRITTENSTADPREM)monthYt from FACT_YX_PREPREM_LIST_day t where TO_CHAR(t.YT_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM')  \n" +
            "AND t.RNFLAG='N' AND t.DEPTCODE1 IS NOT NULL GROUP BY t.SALEDEPTCODE,t.branchcode)b on a.SALEDEPTCODE=b.SALEDEPTCODE where (a.monthInSUre-b.monthYt)>='300000')a group by a.BRANCHCODE)a right join\n" +
            "SD_BRANCH sb on a.BRANCHCODE=sb.BRANCHCODE)yr,\n" +
            "(select sb.branchsname,round(bnc.branchYearInSure/sb.writtentarget/100,1) yearReachYield from SD_BRANCH sb left join (\n" +
            "        select bcb.BRANCHSNAME,bcb.zcb-byt.zyt branchYearInSure from \n" +
            "(select SD_BRANCH.BRANCHSNAME,NVL(cb.yearInSure,0) zcb from\n" +
            "(select BRANCHCODE, SUM(WRITTENSTADPREM)yearInSure from FACT_YX_PREPREM_LIST_day where  TO_CHAR(ISSUE_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY') AND RNFLAG='N' GROUP BY BRANCHCODE)cb RIGHT JOIN\n" +
            "SD_BRANCH ON cb.BRANCHCODE=SD_BRANCH.BRANCHCODE)bcb left join\n" +
            "(select SD_BRANCH.BRANCHSNAME,NVL(yt.yearYt,0) zyt from\n" +
            "(select BRANCHCODE, SUM(WRITTENSTADPREM)yearYt from FACT_YX_PREPREM_LIST_day where  TO_CHAR(YT_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY')  AND RNFLAG='N' GROUP BY BRANCHCODE)yt right join \n" +
            "SD_BRANCH ON yt.BRANCHCODE=SD_BRANCH.BRANCHCODE)byt\n" +
            "on bcb.BRANCHSNAME=byt.BRANCHSNAME \n" +
            ") bnc on bnc.branchsname=sb.branchsname)nr\n" +
            "where y.BRANCHSNAME=qy.BRANCHSNAME and qy.BRANCHSNAME=j.BRANCHSNAME and j.BRANCHSNAME=qj.BRANCHSNAME and qj.BRANCHSNAME=n.BRANCHSNAME and n.BRANCHSNAME=qn.BRANCHSNAME and qn.BRANCHSNAME=yr.BRANCHSNAME\n" +
            "and yr.BRANCHSNAME=nr.BRANCHSNAME  order by n.branchYearInSure desc")
    List<BranchReach> getBranchReach();
}
